Imports System.Data.SqlClient

Public Class <OwnerU>_<TableU>Data

    Public Shared Function SelectAll() As DataTable
        Dim connection As SqlConnection = <Database>Data.GetConnection
        Dim selectProcedure As String = "[<Owner>].[<TableU>SelectAll]"
        Dim selectCommand As New SqlCommand(selectProcedure, connection)
        selectCommand.CommandType = CommandType.StoredProcedure
        Dim dt As New DataTable
        Try
            connection.Open()
            Dim reader As SqlDataReader = selectCommand.ExecuteReader()
            If reader.HasRows Then
                dt.Load(reader)
            End If
            reader.Close()
        Catch ex As SqlException
            Return dt
        Finally
            connection.Close()
        End Try
        Return dt
    End Function

    Public Shared Function Search(ByVal sField As String, ByVal sCondition As String, ByVal sValue As String) As DataTable
        Dim connection As SqlConnection = <Database>Data.GetConnection
        Dim selectProcedure As String = "[<Owner>].[<TableU>Search]"
        Dim selectCommand As New SqlCommand(selectProcedure, connection)
        selectCommand.CommandType = CommandType.StoredProcedure
    <If Loop>
   	<IsNotText>
   	<And>
   	<IsNotNText>
        <And>
        <IsRelated>
	       <And>
	       <IsMiscTag>
        selectCommand.Parameters.AddWithValue("@<RDisplayField><MiscTag>", IIf(sField = "<FieldC>", sValue, DBNull.Value))
   	<IsNotText>
   	<And>
   	<IsNotNText>
        <And>
        <IsRelated>
	       <And>
	       <IsNotMiscTag>
        selectCommand.Parameters.AddWithValue("@<RDisplayField>", IIf(sField = "<FieldC>", sValue, DBNull.Value))
   	<IsNotText>
   	<And>
   	<IsNotNText>
        <And>
        <IsNotRelated>
        selectCommand.Parameters.AddWithValue("@<FieldU>", IIf(sField = "<FieldC>", sValue, DBNull.Value))
    </If Loop>
        selectCommand.Parameters.AddWithValue("@SearchCondition", sCondition)
        Dim dt As New DataTable
        Try
            connection.Open()
            Dim reader As SqlDataReader = selectCommand.ExecuteReader()
            If reader.HasRows Then
                dt.Load(reader)
            End If
            reader.Close()
        Catch ex As SqlException
            Return dt
        Finally
            connection.Close()
        End Try
        Return dt
    End Function

    Public Shared Function Select_Record(ByVal <OwnerU>_<TableU>Para As <OwnerU>_<TableU>) As <OwnerU>_<TableU>
        Dim <OwnerU>_<TableU> As New <OwnerU>_<TableU>
        Dim connection As SqlConnection = <Database>Data.GetConnection
        Dim selectProcedure As String = "[<Owner>].[<TableU>Select]"
        Dim selectCommand As New SqlCommand(selectProcedure, connection)
        selectCommand.CommandType = CommandType.StoredProcedure
        <If Loop>
           <IsPKey>
        selectCommand.Parameters.AddWithValue("@<FieldU>", <OwnerU>_<TableU>Para.<FieldU>)
        </If Loop>
        <If Loop>
           <IsNotPKey>
           <And>
           <IsIdentity>
        selectCommand.Parameters.AddWithValue("@<FieldU>", <OwnerU>_<TableU>Para.<FieldU>)
        </If Loop>
        <If Loop>
           <IsNotIdentityCount>
           <And>
           <IsNotPKeyCount>
   	   <And>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
        selectCommand.Parameters.AddWithValue("@<FieldU>", <OwnerU>_<TableU>Para.<FieldU>)
           <ExitLoop>
        </If Loop>
        Try
            connection.Open()
            Dim reader As SqlDataReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow)
            If reader.Read Then
                With <OwnerU>_<TableU>
                <If Loop>
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsFloat>
                    .<FieldU> = If(IsDBNull(reader("<FieldU>")), Nothing, CType(CType(reader("<FieldU>"), Double?), Decimal?))
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsReal>
                    .<FieldU> = If(IsDBNull(reader("<FieldU>")), Nothing, CType(CType(reader("<FieldU>"), Single?), Decimal?))
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsNotFloat>
                    <And>
                    <IsNotReal>
                    .<FieldU> = If(IsDBNull(reader("<Field>")), Nothing, CType(reader("<Field>"), <VBType>?))
                    <IsNull>
                    <And>
                    <IsNotNullable>
                    .<FieldU> = If(IsDBNull(reader("<Field>")), Nothing, reader("<Field>").ToString)
                    <IsNotNull>
                    .<FieldU> = <Convert>(reader("<Field>"))
                </If Loop>
                End With
            Else
                <OwnerU>_<TableU> = Nothing
            End If
            reader.Close()
        Catch ex As SqlException
            Return <OwnerU>_<TableU>
        Finally
            connection.Close()
        End Try
        Return <OwnerU>_<TableU>
    End Function

    Public Shared Function Add(ByVal <OwnerU>_<TableU> As <OwnerU>_<TableU>) As Boolean
        Dim connection As SqlConnection = <Database>Data.GetConnection
        Dim insertProcedure As String = "[<Owner>].[<TableU>Insert]"
        Dim insertCommand As New SqlCommand(insertProcedure, connection)
        insertCommand.CommandType = CommandType.StoredProcedure
        <If Loop>
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        insertCommand.Parameters.AddWithValue("@<FieldU>", IIf(<OwnerU>_<TableU>.<FieldU>.HasValue, <OwnerU>_<TableU>.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        insertCommand.Parameters.AddWithValue("@<FieldU>", IIf(Not IsNothing(<OwnerU>_<TableU>.<FieldU>), <OwnerU>_<TableU>.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNotNull>
        insertCommand.Parameters.AddWithValue("@<FieldU>", <OwnerU>_<TableU>.<FieldU>)
        </If Loop>
        insertCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int)
        insertCommand.Parameters("@ReturnValue").Direction = ParameterDirection.Output
        Try
            connection.Open()
            insertCommand.ExecuteNonQuery()
            Dim count As Integer = System.Convert.ToInt32(insertCommand.Parameters("@ReturnValue").Value)
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As SqlException
            Return False
        Finally
            connection.Close()
        End Try
        Return True
    End Function

    Public Shared Function Update(ByVal <OwnerU>_<TableU>Old As <OwnerU>_<TableU>, _
           ByVal <OwnerU>_<TableU>New As <OwnerU>_<TableU>) As Boolean
        Dim connection As SqlConnection = <Database>Data.GetConnection
        Dim updateProcedure As String = "[<Owner>].[<TableU>Update]"
        Dim updateCommand As New SqlCommand(updateProcedure, connection)
        updateCommand.CommandType = CommandType.StoredProcedure
        <If Loop>
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        updateCommand.Parameters.AddWithValue("@New<FieldU>", IIf(<OwnerU>_<TableU>New.<FieldU>.HasValue, <OwnerU>_<TableU>New.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        updateCommand.Parameters.AddWithValue("@New<FieldU>", IIf(Not IsNothing(<OwnerU>_<TableU>New.<FieldU>), <OwnerU>_<TableU>New.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNotNull>
        updateCommand.Parameters.AddWithValue("@New<FieldU>", <OwnerU>_<TableU>New.<FieldU>)
        </If Loop>
        <If Loop>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNotNull>
        updateCommand.Parameters.AddWithValue("@Old<FieldU>", <OwnerU>_<TableU>Old.<FieldU>)
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        updateCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(<OwnerU>_<TableU>Old.<FieldU>.HasValue, <OwnerU>_<TableU>Old.<FieldU>, DBNull.Value))
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        updateCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(Not IsNothing(<OwnerU>_<TableU>Old.<FieldU>), <OwnerU>_<TableU>Old.<FieldU>, DBNull.Value))
        </If Loop>
        updateCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int)
        updateCommand.Parameters("@ReturnValue").Direction = ParameterDirection.Output
        Try
            connection.Open()
            updateCommand.ExecuteNonQuery()
            Dim count As Integer = System.Convert.ToInt32(updateCommand.Parameters("@ReturnValue").Value)
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As SqlException
            Return False
        Finally
            connection.Close()
        End Try
        Return True
    End Function

    Public Shared Function Delete(ByVal <OwnerU>_<TableU> As <OwnerU>_<TableU>) As Boolean
        Dim connection As SqlConnection = <Database>Data.GetConnection
        Dim deleteProcedure As String = "[<Owner>].[<TableU>Delete]"
        Dim deleteCommand As New SqlCommand(deleteProcedure, connection)
        deleteCommand.CommandType = CommandType.StoredProcedure
        <If Loop>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNotNull>
        deleteCommand.Parameters.AddWithValue("@Old<FieldU>", <OwnerU>_<TableU>.<FieldU>)
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        deleteCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(<OwnerU>_<TableU>.<FieldU>.HasValue, <OwnerU>_<TableU>.<FieldU>, DBNull.Value))
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        deleteCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(Not IsNothing(<OwnerU>_<TableU>.<FieldU>), <OwnerU>_<TableU>.<FieldU>, DBNull.Value))
        </If Loop>
        deleteCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int)
        deleteCommand.Parameters("@ReturnValue").Direction = ParameterDirection.Output
        Try
            connection.Open()
            deleteCommand.ExecuteNonQuery()
            Dim count As Integer = System.Convert.ToInt32(deleteCommand.Parameters("@ReturnValue").Value)
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As SqlException
            Return False
        Finally
            connection.Close()
        End Try
        Return True
    End Function

End Class
